#!/usr/bin/php -q
<?php

  /*
   * This script is written for filling missing parentID and parentNames in the 
   * Country Checklist based on occurrence. Before this script, parent-child
   * relationships are constructed according to higher hierarchy of nub taxonomy,
   * which however could have missing values in various ranks. This script then
   * fills missing values using parentID and make sure the new value allows the
   * child to associate to valid parent and goes on.
   *
   */

  define('GBIF_USAGE_URL', 'http://api.gbif.org/name_usage/');
  define('GBIF_USAGE_SOLR', 'http://jawa.gbif.org:8080/checklistbank-solr/select/?version=2.2&start=0&rows=10&indent=on&wt=json&q=');
  define('GBIF_NUB_KEY', 'd7dddbf4-2cf0-4f39-9b2a-bb099caae36c');
  define('GBIF_CHECKLISTBANK_SOLR', 'http://jawa.gbif.org:8080/checklistbank-solr/select/?version=2.2&start=0&rows=10&indent=on&wt=json&q=%28');
  // Reference format: http://jawa.gbif.org:8080/checklistbank-solr/select/?version=2.2&start=0&rows=10&indent=on&wt=json&q=%28family:labridae%20AND%20checklist_key:d7dddbf4-2cf0-4f39-9b2a-bb099caae36c%29
  
  // Database credentials
  require_once('settings.php');
  
  select_database('portal');
  $target_table = 'parent_child_bko';
  $hierarchy = array(
    '1000' => 'kingdom',
    '2000' => 'phylum',
    '3000' => 'class',
    '4000' => 'order',
    '5000' => 'family',
    '6000' => 'genus',
    '7000' => 'species',
    '8000' => 'subspecies',
    '8010' => 'variety',
    '8020' => 'form',
  );
  
  $DBH = new PDO("mysql:host=$host;dbname=$database", $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
  $DBH->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql';

  $to_add = array();
  $to_analyse = array();
  $to_match_by_name = array();
  
  function get_usage_json($id) {
    global $hierarchy;
    $usage = array();
    $url = GBIF_USAGE_URL . $id;
    $JSON = file_get_contents($url);
    if ($JSON != FALSE) {
      $usageJSON = json_decode(file_get_contents($url));
      $usage['id'] = (int)$id;
      $usage['scientificName'] = $usageJSON->scientificName;
      $usage['parentID'] = (int)$usageJSON->parentKey;
      $usage['parentName'] = $usageJSON->parent;
      $usage['authorship'] = $usageJSON->authorship;
      $usage['rank'] = strtolower($usageJSON->rank);
      $usage['rank_id'] = array_search($usage['rank'], $hierarchy);

      if ($usage['rank'] == 'subspecies') {
        $usage['unitIdicator3'] = 'subsp.';        
      } elseif ($usage['rank'] == 'variety') {
        $usage['unitIdicator3'] = 'var.';        
      } elseif ($usage['rank'] == 'form') {
        $usage['unitIdicator3'] = 'f.';
      } elseif ($usage['rank'] == 'infraspecific') {
        $usage['unitIdicator3'] = NULL;        
      } else {
        $usage['unitIdicator3'] = NULL;
      }
      
      switch ($usage['rank']) {
        case 'kingdom':
        case 'phylum':
        case 'class':
        case 'order':
        case 'family':
        case 'genus':
          $usage['unitName1'] = $usage['scientificName'];
          $usage['unitName2'] = NULL;
          $usage['unitName3'] = NULL;
          break;
        case 'species':
          $units = explode(' ', $usage['scientificName']);
          $usage['unitName1'] = $units[0];
          $usage['unitName2'] = $units[1];
          $usage['unitName3'] = NULL;
          break;
        case 'subspecies':
        case 'variety':
        case 'form':
        case 'infraspecific':
          $units = explode(' ', $usage['scientificName']);
          $usage['unitName1'] = $units[0];
          $usage['unitName2'] = $units[1];
          $usage['unitName3'] = $units[2];
          break;
      }
      
      return $usage;
    } else {
      return FALSE;
    }
  }
  function check_missing_parent($conn, $target_table) {
    // Getting all entries that are missing parentID, except Kingdom names
    $sql = "SELECT * FROM ".$target_table." WHERE parentID IS NULL AND rank_id > 1000 ORDER BY rank_id DESC";
    $find_species = $conn->query($sql);
    $find_species->setFetchMode(PDO::FETCH_ASSOC);
    $results = $find_species->fetchAll();
    return $results;
  }
  function check_parent_exist($conn, $parentID, $target_table) {
    $sql = "SELECT count(*) AS exist FROM ".$target_table." WHERE id = ".$parentID;
    $check_exist = $conn->query($sql);
    $check_exist->setFetchMode(PDO::FETCH_ASSOC);
    $exist = $check_exist->fetch();
    return $exist['exist'];
  }
  function check_child_exist($conn, $target_table, $id) {
    $sql = "SELECT count(*) AS referred FROM ".$target_table." WHERE parentID = ".$id;
    $check_referred = $conn->query($sql);
    $check_referred->setFetchMode(PDO::FETCH_ASSOC);
    $referred = $check_referred->fetch();
    return $referred['referred'];
  }
  function get_entry($conn, $target_table, $id) {
    $sql = "SELECT id, scientificName FROM ".$target_table. " WHERE id = ". $id;
    $query = $conn->query($sql);
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $result = $query->fetch();
    return $result;
  }
  function fill_parent_id($conn, $parentID, $parentName, $target_table, $id) {
    $updateSQL = '';
    $updateSQL = "UPDATE ".$target_table." SET parentID = :parentID, parentNameUsage = :parentName WHERE id = :id";
    $update = $conn->prepare($updateSQL);
    $id = (int)$id;
    $update_result = $update->execute(array(':parentID' => $parentID, ':parentName' => $parentName, ':id' => $id));
    $result = ($update_result == TRUE) ? 'done' : 'failed';
    return $result;
  }
  function insert_parent_entry($conn, $target_table, $id, $scientificName, $parentID, $parentName, $author, $rank, $unitIndicator3, $unitName1, $unitName2, $unitName3, $rank_id) {
    $insertSQL = "INSERT INTO ".$target_table." (id, scientificName, parentID, parentNameUsage, author, rank, unitIndicator3, unitName1, unitName2, unitName3, rank_id) values (:id, :scientificName, :parentID, :parentName, :author, :rank, :unitIndicator3, :unitName1, :unitName2, :unitName3, :rank_id)";
    $insert = $conn->prepare($insertSQL);
    $insert_result = $insert->execute(array(':id' => $id, ':scientificName' => $scientificName, ':parentID' => $parentID, ':parentName' => $parentName, ':author' => $author, ':rank' => $rank, ':unitIndicator3' => $unitIndicator3, ':unitName1' => $unitName1, ':unitName2' => $unitName2, ':unitName3' => $unitName3, ':rank_id' => $rank_id));
    $result = ($insert_result == TRUE) ? 'done' : 'failed';
    return $result;
  }
  function delete_entry($conn, $target_table, $id) {
    $deleteSQL = "DELETE FROM ".$target_table." WHERE id = :id";
    $delete = $conn->prepare($deleteSQL);
    $delete_result = $delete->execute(array(':id' => $id));
    $result = ($delete_result == TRUE) ? 'done' : 'failed';
    return $result;
  }
  function rank_sort($a, $b) {
    $order = array_flip(array('species', 'genus', 'family', 'order', 'class', 'phylum', 'kingdom'));
    foreach (array($a, $b) as $arg) {
      if(!array_key_exists($arg, $order)) trigger_error("`$arg' is not a sortable key");
    }
    return $order[$a] - $order[$b];
  }
  
  fwrite(STDOUT, "Starting to analyse $target_table table...\n");
  $results_count = count(check_missing_parent($DBH, $target_table));
  fwrite(STDOUT, "$results_count record(s) with missing parentID.\n");
  
  while (count(check_missing_parent($DBH, $target_table)) >= 1) {

    // Getting all entries that are missing parentID, except Kingdom names
    fwrite(STDOUT, "Getting entries that are missing parentID...\n");
    $results = check_missing_parent($DBH, $target_table);
    $results_count = count($results);
    fwrite(STDOUT, "$results_count record(s) with missing parentID.\n");

    // Process record by record
    foreach ($results as $result) {

      $id = $result['id'];
      $scientificName = $result['scientificName'];
      $rank = $result['rank'];
      $rank_id = $result['rank_id'];

      // Show the record being processed...
      fwrite(STDOUT, "$id\t$scientificName\t$rank\t$rank_id\n");

      $usage = get_usage_json($id);
      if ($usage != FALSE) {
        $parentID = $usage['parentID'];
        $parentName = $usage['parentName'];      

        if (isset($parentID) && isset($parentName)) {

          fwrite(STDOUT, "  ->  $parentID\t$parentName\t");

          // Check if the parentID points to an existing record
          $exist_status = check_parent_exist($DBH, $parentID, $target_table);

          if ($exist_status >= 1) {

            // The parentID points to an existing record, parentID and parentName can be added directly.
            fwrite(STDOUT, "... $exist_status parent exists. Filling parentID and parentName...");
            $result = fill_parent_id($DBH, $parentID, $parentName, $target_table, $id);
            fwrite(STDOUT, "$result\n\n");

          } elseif ($exist_status == 0) {

            // The parentID doesn't point to any existing record. The parent need to be added
            // and checked if the parent of the parent points to an existing record.
            // To process in a separate loop.
            fwrite(STDOUT, "... will be parsed and added later.\n\n");
            if (in_array($parentID, $to_add) == FALSE) $to_add[] = $parentID;
          }
        }

      } elseif ($usage == FALSE) {
        // Obviouly missing even parentID from nub usage.
        // Could still try by name matching.
        // To process in a separate loop.
        fwrite(STDOUT, "  ->  non-match\n\n");
        if (count($to_match_by_name) > 0) {
          $in_to_match = FALSE;
          foreach ($to_match_by_name as $k => $v) {
            if ($v['id'] == $result['id']) $in_to_match = TRUE;
          }
        }
        if ($in_to_match == FALSE) $to_match_by_name[] = $result;
      }
    }


    if (count($to_add) > 0) {
      fwrite(STDOUT, "\nAdding new entries... \n");
      foreach ($to_add as $key => $id) {
        fwrite(STDOUT, "$id, "); 

        $usage = get_usage_json($id);
        if ($usage != FALSE) {
          $parentID = $usage['parentID'];
          $parentName = $usage['parentName'];      

          if (isset($parentID) && isset($parentName)) {

            fwrite(STDOUT, "  ->  $parentID\t$parentName\t");

            // Check if the parentID points to existing record
            $exist_status = check_parent_exist($DBH, $parentID, $target_table);

            if ($exist_status >= 1) {
              // The new entry will immediately hook on an existing entry.
              fwrite(STDOUT, "... $exist_status parent exists. ");

              // Insert the new entry.
              fwrite(STDOUT, "Inserting the new parent entry...");
              $result = insert_parent_entry($DBH, $target_table, $usage['id'], $usage['scientificName'], $usage['parentID'], $usage['parentName'], $usage['authorship'], $usage['rank'], $usage['unitIdicator3'], $usage['unitName1'], $usage['unitName2'], $usage['unitName3'], $usage['rank_id']);
              fwrite(STDOUT, "$result\n\n");
              unset($to_add[$key]);

            } elseif ($exist_status == 0) {
              // The new entry will require additional entry to be hooked on.
              // A different look-up strategy needs to be taken.
              fwrite(STDOUT, "... parent doesn't exist. \n");
              $usage_2nd = get_usage_json($parentID);
              if ($usage_2nd != FALSE) {
                $parentID_2nd = $usage_2nd['parentID'];
                $parentName_2nd = $usage_2nd['parentName'];
                if (isset($parentID_2nd) && isset($parentName)) {
                  fwrite(STDOUT, "  2nd level parent -> $parentID_2nd\t$parentName_2nd\t");
                  $exist_status_2nd = check_parent_exist($DBH, $parentID_2nd, $target_table);
                  if ($exist_status_2nd >= 1) {
                    fwrite(STDOUT, "... $exist_status parent exists. ");
                    fwrite(STDOUT, "Inserting the 2nd level parent entry...");
                    $result = insert_parent_entry($DBH, $target_table, $usage_2nd['id'], $usage_2nd['scientificName'], $usage_2nd['parentID'], $usage_2nd['parentName'], $usage_2nd['authorship'], $usage_2nd['rank'], $usage_2nd['unitIdicator3'], $usage_2nd['unitName1'], $usage_2nd['unitName2'], $usage_2nd['unitName3'], $usage_2nd['rank_id']);
                    fwrite(STDOUT, "$result\n\n");                    
                  } elseif ($exist_status_2nd == 0) {
                    fwrite(STDOUT, "Can't solve by tracing up to 2 levels.\n");
                    if (in_array($parentID, $to_analyse) == FALSE) $to_analyse[] = $parentID;
                  }
                }
              }
            }
          }
        }
      }    
    }

    fwrite(STDOUT, "\n");
    /*
    if (count($to_analyse) > 0) {
      fwrite(STDOUT, "Analysing entries that needs more levels of parent entries...");
    }
    */
    if (count($to_match_by_name) > 0) {
      fwrite(STDOUT, "\nPreparing for matching by name: \n");
      foreach ($to_match_by_name as $key => $result) {

        $to_match_id = $result['id'];
        // Check if the ID is being referred by any existing record
        $referred = check_child_exist($DBH, $target_table, $to_match_id);

        if ($referred >= 1 ) {
          fwrite(STDOUT, "$to_match_id ... will be matched by name.\n");
          // Try to look for possible matches in checklist bank solr nub
          $url = GBIF_CHECKLISTBANK_SOLR . $result['rank'] . ':' . $result['scientificName'] . '%20AND%20checklist_key:' . GBIF_NUB_KEY . '%29';
          $solrJSON = json_decode(file_get_contents($url));
          if (isset($solrJSON)) {
            $docs = $solrJSON->response->docs;
            $r_hierarchy = $hierarchy;
            krsort($r_hierarchy);
            $higher_hierarchy = array();
            // Extract only higher hierarchy from the json result
            foreach ($docs as $dkey => $doc) {
              if ($doc->$result['rank'] == $result['scientificName']) {
                foreach ($hierarchy as $rid => $rname) {
                  if (isset($doc->$rname)) {
                    $rkey = $rname . '_key';
                    $higher_hierarchy[$rname][$dkey] = $doc->$rkey;
                  }
                }
              }
            }
            foreach ($higher_hierarchy as $rname => $v) {
              $higher_hierarchy[$rname] = array_count_values($v);
              if (count($higher_hierarchy[$rname]) > 1) unset($higher_hierarchy[$rname]);
            }
            foreach ($higher_hierarchy as $rname => $v) {
              foreach ($v as $nk => $c) {
                $higher_hierarchy[$rname] = $nk;
              }
            }
            uksort($higher_hierarchy, 'rank_sort');
            foreach ($higher_hierarchy as $rname => $nk) {
              // Only list higher ranks that is higher than the current rank
              if (rank_sort($rname, $result['rank']) >= 1) {
                $exist = check_parent_exist($DBH, $nk, $target_table);
                fwrite(STDOUT, "\t$nk at rank $rname has $exist record(s)\n");
                // First encounter of one common ancestor would be valid for parentID.
                if ($exist == 1) {
                  $query_result = get_entry($DBH, $target_table, $nk);
                  $parentNameUsage = $query_result['scientificName'];
                  fwrite(STDOUT, "\t to insert $nk with parent name $parentNameUsage...");
                  $fill_result = fill_parent_id($DBH, $nk, $parentNameUsage, $target_table, $to_match_id);
                  fwrite(STDOUT, "$fill_result.\n");
                  unset($to_match_by_name[$key]);
                  break;
                }
              }
            }
          } else {
            // Unfinished look-up by name on usage url.
            $usageByName_url = GBIF_USAGE_URL . $result['scientificName'];
            $usageByName = json_decode(file_get_contents($usageByName_url));
            if (isset($usageByName)) {
              foreach ($usageByName as $doc) {
                if ($doc['canonicalName'] == $result['scientificName'] && strtolower($doc['rank']) == $result['rank'] && $doc['checklistKey'] == GBIF_NUB_KEY) {
                  fwrite(STDOUT, "Name matched on the usage webservice.");
                }
              }
            } else {
              fwrite(STDOUT, "Currenly none of the matching stragies works.");
            }
          }
          fwrite(STDOUT, "\n");
        } elseif ($referred == 0) {
          fwrite(STDOUT, "$to_match_id ... is not being referred. Deleting to avoid inaccuracy...");
          $result = delete_entry($DBH, $target_table, $to_match_id);
          fwrite(STDOUT, "$result\n");
          unset($to_match_by_name[$key]);
        }
      }    
    }

    // List all remaining ids
    if (count($to_add) > 0) {
      fwrite(STDOUT, "\nRemained to be added:\n");
      foreach ($to_add as $id) fwrite(STDOUT, "$id, ");
      fwrite(STDOUT, "\n");
    }
    if (count($to_analyse) > 0) {
      fwrite(STDOUT, "\nRemained to be analysed:\n");
      foreach ($to_analyse as $id) fwrite(STDOUT, "$id, ");
      fwrite(STDOUT, "\n");
    }
    if (count($to_match_by_name) > 0) {
      fwrite(STDOUT, "\nRemained to be matched by name:\n");
      foreach ($to_match_by_name as $v) {
        $to_match_id = $v['id'];
        fwrite(STDOUT, "$to_match_id, ");
      }
      fwrite(STDOUT, "\n\n");
    }    
  }

  $DBH = null;
?>